壓縮表面上是節省空間，犧牲效能，但對於SQL Server而言並不全然如此。

SQL Server 的壓縮實際上通常都可以帶來效能上的好處。

1. SQL Server 是一種受 I/O 限制的應用程式，而不是受 CPU 限制。
2. 所以如果SQL Server 需要從硬碟讀取的資料頁大幅減少，那麼即使是以消耗 CPU 周期為代價，效能仍然會提升。
3. 那當然，如果資料庫有非常快速的硬碟，但是只有單核心 CPU，那壓縮就會造成負面影響，因為壓縮會額外使用 CPU；不過實際應用上，從來都沒看過這種情況。

要壓縮前首先要先了解，頁的結構。

Page 頁

結構

一個 Page 固定是 8 KB，他的結構大致分成三個區塊如下

1. Page Header ( 96 bytes )
2. Data Area
3. Row Offset Array ( 2 bytes )

+--------------------------------------------------+
| Page Header
| 96 bytes
+--------------------------------------------------+
| Data Area
| 一筆一筆資料列 Row 實際存放的地方
| Row 1
| Row 2
| Row 3
| ...
+--------------------------------------------------+
| Row Offset Array
| 每一列在 Page 裡面的起始位置
+--------------------------------------------------+

Page Header 從最前面開始長出來。

Row 資料也通常從前面往後放。

Row Offset Array 則是從 Page 最後面往前長

Page Header

每一個 Page 一定會有的這個，他是作為 metadata 使用，會存以下內容

讓 SQL Server 知道：
這一頁是誰、屬於哪個資料表或索引、裡面有幾筆資料、還剩多少空間、跟哪些頁面相連、最後被哪個交易修改過。

Data Area

緊跟 Header 之後的就是 Data Area，實際放一筆一筆資料列的地方

Row Offset Array

Page 最後面有 Row Offset Array 。

作用是記錄每一筆 Row 在 Page 裡面的起始位置。

每一筆 Row 對應一個 2 bytes 的 offset。

例如 Page 裡有 5 筆資料列
Row Offset Array
Slot 0 -> Row 1 的起始位置
Slot 1 -> Row 2 的起始位置
Slot 2 -> Row 3 的起始位置
Slot 3 -> Row 4 的起始位置
Slot 4 -> Row 5 的起始位置

這個要理解成，SQL Server 在 Page 裡定位資料列，不是說這一筆排在哪一個物理磁區上，是透過 Slot 找到 Row 位置。

所以會是這樣 : File 1 ,Page 12345 ,Slot 7

意思是 : 第 1 個資料檔、第 12345 頁、Row Offset Array 裡第 7 個槽位指向的那筆資料列。

這就是 SQL Server 內部常見的 RID。
Heap Table 裡的 Nonclustered Index 就會用 RID 指向實際資料列。

Row

這個放在 Data Area 裡面實際的資料。

一筆 Row 可以分成

• Row Header / Row Metadata
• Fixed-length Column Metadata
• Fixed-length Columns
• NULL Bitmap
• Variable-length Column Metadata
• Variable-length Columns
• Optional Versioning / Extra Metadata

Row Header / Row Metada

跟 Page 一樣，每一筆 Row 也都會有自己的 Header，資訊會包含 :

Row Metadata 的核心功能是讓 SQL Server 能夠快速判斷：
這筆 Row 裡面有哪些區塊、哪些欄位類型、後面的資料該怎麼解讀。

Fixed-length Columns 固定長度欄位

固定長度欄位資料會放在這個區域。

這有一個好處因為長度固定，SQL Server 很容易就可以算出欄位的位置。

Variable-length Columns 可變長度欄位

可變長度欄位資料會放在這個區域。

Variable-length Column Offset Array

可變長度欄位有一個麻煩：
每個欄位長度都不一樣，所以 SQL Server 不能單純用固定位置找到欄位。

假設有資料如下
Name = N'王小明'
Phone = '0912345678'
Email = 'abc@gmail.com'

SQL Server 必須知道 :

• Name 到哪裡結束?
• Phone 從哪裡開始?
• Email 從哪裡開始?

所以資料列都會有 Variable-length Column Offset Array
跟 Page 上的 Offset 一樣，這裡是用來記錄可變動長度欄位的結束位置的；這樣 SQL Server 就可以透過這個紀錄來推算下一個欄位的起始位置。

NULL Bitmap

SQL Server 怎麼紀錄 NULL?

只要資料表允許 NULL，SQL Server 需要知道哪個欄位是 NULL。

它不會在每個欄位旁邊寫一串文字說「這個是 NULL」。

它會用 NULL Bitmap。

NULL Bitmap 是一段位元陣列，每個欄位對應一個 bit。

例如一張表有 8 個欄位：

Column 1 -> bit 1
Column 2 -> bit 2
Column 3 -> bit 3
...
Column 8 -> bit 8

然後 NULL Bitmap 紀錄 00000100

那就代表第 3 個欄位是 NULL。( 從後面數回來 )

一筆 ROW 的大小限制

SQL Server 一個 Page 是 8KB。

但是一筆資料列本身也有限制，一般 In-row Data 的單筆字料列大小上限大約是 8060 bytes。

因為 Page 需要 Header、Row Offset Array 這些會占空間。

那如果一筆資料列太大，SQL Server 無法放在同一個 Page的時候，這時候會牽涉到

1. Row-overflow Data
2. LOB Data
3. Off-row Storage

Row-overflow Data

理論上這三個欄位可以非常大，如果某一筆 Row 實際資料太大，超過 In-row 限制，SQL Server 會把部分可變長度欄位移到其他 Page，原本的 Row 只保留一個 pointer。
這就叫 Row-overflow Data。

原本資料列：
+--------------------------+
| 固定長度欄位
| 部分可變長度欄位
| pointer -> 其他資料頁
+--------------------------+

其他資料頁：
+--------------------------+
| 放不下的可變長度欄位資料
+--------------------------+
而這樣的設計會影響效能，因為 SQL Server 查這筆資料時，不只讀原本的資料頁，還要額外讀 Row-overflow Page。

LOB Data 大型物件

這類資料如果很大，通常不會完整放在原本的資料列裡，而是放到 LOB Page。
原本的 Row 裡面只保留指向 LOB 資料的 pointer。

Data Page 裡的 Row
+-------------------------+
| ID
| Name
| LOB pointer
+-------------------------+

LOB Page
+-------------------------+
| 很長的 nvarchar(max)
| 很大的 varbinary(max)
+-------------------------+

這也是為什麼查詢時不要沒必要就 SELECT *。

如果你把大型欄位也一起抓出來，SQL Server 可能要額外讀很多 LOB Page，I/O 成本會變高。

Heap Table 和 Clusstered Table 的 Page 差異

SQL Server 資料表主要有兩種物理儲存型態：

1. Heap
2. Clustered Table (也就是 b-tree)

Heap 是沒有 Clustereed Index 的表。
資料沒有按照 clustered key 排序。

新資料通常找有空間的 page 塞進去。
Heap 裡的 Row 位置用 RID 代表。

Heap 的問題是，如果某筆資料更新後變大，原本 page 放不下，SQL Server 會產生 Forwarded Record。

Forwarded Record，是 Heap 特有的重要概念。
假設原本 Row 在 Page A，後來更新某個 varchar 欄位，資料變大，Page A 放不下。

SQL Server 會把真正的 Row 搬到 Page B，然後在 Page A 留下一個 forwarding pointer。
這會讓查詢多一次跳轉。

所以 Heap 如果大量更新可變長度欄位，效能可能會變差。

Page A
+---------------------+
| Forwarding pointer | ---> Page B 的新位置
+---------------------+

Page B
+---------------------+
| 真正的資料列 |
+---------------------+

關於 heap、b-tree 會在索引篇詳細說明，前面一直提就是為了加深一點印象，從簡單的理解開始。

Clustered Table 是有 Clustered Index 的表。

資料列存在 Clustered Index 的 Leaf Level。
也就是說，Clustered Index 的最底層葉節點就是實際資料列。

資料會依照 Clustered Key 的邏輯順序排列。

Clustered Table 不使用 RID 來當主要定位方式，而是用 Clustered Key 來定位資料列。

Nonclustered Index 指向 Clustered Table 的資料時，葉層通常會存 Clustered Key，而不是 RID。

管理用的 Pages

Extent 是 Page 的上一層單位

SQL Server 不只用 Page 管理空間，也用 Extent。

SQL Server 早期小物件可能會先用 Mixed Extent，資料量變大後改用 Uniform Extent。新版 SQL Server 對混合 Extent 的預設配置策略已有調整，但 Page 和 Extent 的概念不變。

Allocation Unit

資料實際上分成三類儲存，前面都已經介紹過了

這非常重要，因為看到一張表的時候，不代表他的所有資料都在同一種 Page，所以實際查詢成本會比想像的更高。

Row 壓縮

大致了解完整個 Page 的結構之後，才會比較懂什麼是壓縮。

在未壓縮的資料頁上，如同上面說的，SQL Server 會先儲存固定長度的欄位，接著再存可變長度欄位。

所以當使用可變長度欄位儲存時，他自然會去縮小自己的欄位長度，然而當一張資料表作資料列壓縮時，SQL Server 就會對其他資料型別也會使用最小所需的儲存空間。

例如有一個 int 欄位如下

• 第 1 列包含 NULL
• 第 2 列包含數值 50
• 第 3 列包含數值 40000
  而 int 本身是 4 bytes，如果今天去壓縮 ROW

第 1 列，這個欄位完全不會使用任何空間；

第 2 列，他會占用 1 byte，因為 SQL Server 會把它儲存成 tinyint；

第 3 列，他會使用 4 byte，因為他還是必須用 int 去存。

可以看出這跟沒有壓縮資料表不同，原本無論存什麼資料，只要欄位設計是 int 那就必定占用 4 byte，而壓縮後的資料，實際上是SQL Server 會根據裡面的資料，去選擇相應的資料型態。

如果壓縮之後，那麼 ROW 裡面的格式會從上面講的那樣變成

+-------------------------------+
| Row metadata
+-------------------------------+
| Column descriptor
+-------------------------------+
| Short columns
+-------------------------------+
| Long columns
+-------------------------------+
| Row metadata
+-------------------------------+

Page 壓縮

如果去實作 page 壓縮，SQL Server 會先實作 Row 壓縮。

page 壓縮本身實際上是由兩種不同形式的壓縮所組成。

第一種是 prefix compression，前墜壓縮

第二種是 dictionary compression，字典壓縮。

Prefix 壓縮

前墜壓縮的運作方式，是在同一個資料”頁”內，針對某個欄位跨多筆資料列建立一個共同前墜。

意思就是 : SQL Server 發現同一”頁”裡很多資料很像、很多字重複，所以不要每一列都完整存一次。

假設有一個 Page 裡面有這些資料
Postfreeze
Postman
Postoffice
Postcard
可以觀察到，他們的前面都有 Post 這 4 個字。

1. 所以為了節省空間，SQL Server 就把 Post 這個字標記起來，然後 SQL Server 會去找一個最適合的錨定列起來當作示範，例如這邊選 Postfreeze
2. 接下來，其他資料就不用完整存了，只要存根錨定值相筆差在哪就可以了
3. 例如 Postman，前4個字元相同，所以就存成 <4>man

前綴壓縮不是壓縮整張表，他是壓縮 PAGE，只有在 PAGE 裡面有共同前綴才會被壓縮，PAGE 1、PAGE 2、PAGE 3 也許會有不同的前綴。

因此，Page 壓縮的效果很受資料排列的影響。

如果同一頁裡的資料長得很像，壓縮效果就好，反之就很差。

Dictionary 壓縮

前綴壓縮只處理「開頭重複」。

但是有些資料不是開頭重複，而是「某個值一直重複出現」。

例如同一個 Page 裡有這些資料：
狀態欄位：
Active
Active
Active
Inactive
Active
Inactive

這時候 SQL Server 會在 Page 裡面作一本小字典，按照這個例子他會把 Active 標記成 1、Inactive 標記成 2，然後資料存成 111212，節省空間。

字典壓縮跟前綴壓縮還有一個不同地方是，前綴壓縮只看同一欄，而字典壓縮會整個page 一起看
ProductName Category
Hammock Hammock
Hammock Hammock
Hammock Hammock

Hammock 可能在不同欄位、不同列中出現。
再更深入的話，微軟有說過，他不管資料型別，他是看底層二進位內容有沒有重複。

Page 壓縮後結構

首先 page 壓縮的執行順序是

1. Row
2. Prefix
3. Dictionary

再最一開始有說到 page 的結構是

Header

DATA

SLOT 1

SLOT 2

…

SLOT n

Row Offset Array

再經過 page 壓縮之後，結構會多一個 CI Record 變成這樣
Header

CI Record

DATA

SLOT 1

SLOT 2

…

SLOT n

Row Offset Array
而 CI Record 裡面紀錄的內容，就是前綴壓縮用的錨點、字典壓縮用的 dictionary、各種 offset讓 SQL Server 知道資料放在哪裡

Row & Page 壓縮重點

如果執行 SQL Server 壓縮後發現節省空間不明顯，要回頭檢查資料表是否真的具備可壓縮特徵，就是檢查壓縮的底層原理。

1. 先看欄位型別。
   如果大部分欄位本來就是 varchar、nvarchar、varbinary 這種可變長度欄位，Row Compression 的效果通常比較有限，因為它們原本就不會固定佔滿宣告長度。

2. 再看固定長度欄位裡的實際資料。
   Row Compression 對 int、bigint、decimal、char、nchar、datetime 等固定長度欄位比較有機會節省空間。但如果 int 裡存的值本來就很大，仍然需要接近原本的 bytes 數，壓縮效果就有限。反過來，如果常存 NULL、0、小數值、短字串，效果會比較好。

3. 看同一個 Page 裡是否有共同前綴。
   Page Compression 的 Prefix Compression 會找同一頁中同一欄位的共同開頭。如果像商品編號、代碼、分類路徑這類資料有很長的共同前綴，而且相似資料集中在同一批 Page 裡，壓縮效果會比較好。若相似資料分散在不同 Page，效果會降低。

4. 看同一個 Page 裡是否有大量重複值。
   Page Compression 的 Dictionary Compression 會找同一頁中重複出現的值或壓縮後表示。如果很多欄位值重複，例如狀態、分類、地區、品牌名稱，壓縮效果會比較好。若資料多為 UUID、雜湊值、隨機碼、幾乎不重複的文字，壓縮效果會很差。

Row Compression 主要看「單筆 Row 裡有沒有固定長度浪費」。

Page Compression 主要看「同一個 Page 裡有沒有共同前綴或重複值」。

如果資料本身沒有這些特徵，即使開啟壓縮，也不會節省太多空間，甚至可能因為額外 CPU 成本而不划算。

Columnstore 壓縮

首先有一個原則，Columnstore index 永遠會自動被壓縮。

正確來說，是當資料變更達到十萬筆的時候，自動啟動壓縮。但我知道看不懂，所以就先這樣。

所以如果再 table 上面建立 clustered columnstore index，那表示 table 本身也會被壓縮，而這種壓縮不能和前面提到的 row 跟 page 壓縮結合使用。

因為 Columnstore 是另一套儲存架構，所以不在這裡寫，會寫在效能調教的地方。

XML 壓縮

這個東西是 2022 才引入的功能。

它可以減少存在 overflow allocation units 中的 XML 資料所使用的空間，但代價是消耗 CPU。

小型資料類別的欄位，例如 int ，會存在 page 中。

但是，一筆資料列不能跨越多個資料頁。

像 VARCHAR() 和 XML 這種類型，可能會導致一筆資料列超過資料頁主體中可用的 8kb。

所以 SQL Server 會透過把資料放在和同一張 table 相關聯的獨立 allocation units 中，來處理這個問題。

這些獨立的 allocation units 就叫做 overflow allocation units。

XML 壓縮可以在 XML 欄位上，也可以在 XML index 上。

要注意的是 table-level setting

因此，不能在同一張 table 上，讓不同的 XML 欄位有的開起壓縮有的不開。

Primary XML indexes 不會繼承 table 的 XML 壓縮設定。

Secondary XML index 也同樣不會繼承 Primary XML index 的壓縮設定。

所以，想要一張 table 以及他所有的 index 上使用 XML 壓縮，應當在 Table 和所有 index 上都開啟壓縮。

實作

選擇壓縮層級

從上面說明可得知，page 壓縮的原理 ”通常” 可以提供比 row 壓縮更高的壓縮比例，代表有更好的 I/O 效能。

但是這些都是以 CPU 為代價，不管是資料被壓縮時，或是在 TABLE 被存取時，都會消耗 CPU。

所以在開始之前，必須要先了解

每一種壓縮類型會讓 table 大小減少多少，才能評估可以達成多少 I/O 效率。
可以用一個叫做 sp_estimate_data_compression_savings 的系統 SP 來完成這件事，他會估算如果實作壓縮，可以節省多少空間。

sp_estimate_data_compression_savings 參數

分割壓縮

雖然上面的案例，會發現有壓縮跟沒壓縮是一樣的，不會增加任何 I/O 效能與空間，反而要消耗 CPU 資源沒有任何好處。

但是如果今天預估出來是有效益的，然後也確實按照 ROW / PAGE 去壓縮；但是仔細察看結果，發現只有某個 Partition 能從這次的壓縮受益，其他 partition 都沒有改變，因此為了壓縮某個有效的 partition，而也同時去壓縮其他無效的 partition 所付出的成本是不值得的。

所以可以在壓縮的時候去指定，要壓縮哪一個 partition

維護

維護 Heap 上的壓縮

當新的資料頁被加入到 heap，也就是沒有 clustered index 的資料表時，這些新的資料頁不會自動套用 page compression。

這表示，如果一張已壓縮的資料表沒有 clustered index，那麼重建這張已壓縮資料表，應該成為標準維護流程的一部分。

若要在資料表上重建壓縮，應該先移除壓縮，然後再重新實作壓縮。

下一篇 : 記憶體表